package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.*;
import cn.edu.sdjzu.xg.bysj.service.StudentService;
import cn.edu.sdjzu.xg.bysj.service.TeacherService;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

/**
 * 前 3 个的类型为 Java 自有类型，其中 id 代表对象标识符、title 代表课题名称、score 代
 * 表成绩(Double 类型，在为 null 时，表示无成绩)。
 * 另外，graduateProjectType 代表课题类型。与课题名称可以无限取值不同，课题类型的
 * 取值是有限的，所以使用了一个自定义的类型来表达。graduateProjectCategory、
 * graduateProjectSource 和 graduateProjectTrueness 等三个自定义类型的属性分别代表类别、来
 * 源和性质（真实度）。
 * 由于课题需要多级审核(系主任审核、院长审核)，创建自定义类 GraduateProjectStatus 来
 * 为“当前状态”建模。
 * 系统可能运行多年，故必须有年份字段。
 */

@Slf4j
public final class GraduateProjectDao {
    //新建GraduateProjectDao对象
    private static GraduateProjectDao graduateProjectDao = new GraduateProjectDao();
    //私有构造器
    private GraduateProjectDao(){}
    //获取实例
    public static GraduateProjectDao getInstance(){
        return graduateProjectDao;
    }

    /**
     * 添加毕设
     * @param connection
     * @param graduateProject
     * @return
     * @throws SQLException
     */
    public boolean add(Connection connection,GraduateProject graduateProject) throws SQLException {
        String add_sql = "INSERT INTO GraduateProject (title,score,year," +
                "student_id,teacher_id," +
                "graduateProjectTrueness_id,GraduateProjectStatus_id," +
                "graduateProjectSource_id,graduateProjectCategory_id,graduateProjectType_id) " +
                "VALUES (?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(add_sql);
        //为参数赋值
        preparedStatement.setString(1,graduateProject.getTitle());
        preparedStatement.setDouble(2,graduateProject.getScore());
        preparedStatement.setInt(3,graduateProject.getYear());
        preparedStatement.setInt(4,graduateProject.getStudent().getId());
        preparedStatement.setInt(5,graduateProject.getTeacher().getId());
        preparedStatement.setInt(6,graduateProject.getGraduateProjectTrueness().getId());
        preparedStatement.setInt(7,graduateProject.getGraduateProjectStatus().getId());
        preparedStatement.setInt(8,graduateProject.getGraduateProjectSource().getId());
        preparedStatement.setInt(9,graduateProject.getGraduateProjectCategory().getId());
        preparedStatement.setInt(10,graduateProject.getGraduateProjectType().getId());
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }

    /**
     * 根据id删除
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public boolean delete(Connection connection,int id) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM graduateProject where id =?");
        preparedStatement.setInt(1,id);
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }

    /**
     * 更新指定元组数据
     * @param connection
     * @param graduateProject
     * @return
     * @throws SQLException
     */
    public boolean update(Connection connection,GraduateProject graduateProject) throws SQLException {
        String update_sql = "UPDATE GraduateProject SET " +
                "title=?,score=?,year=?," +
                "student_id=?,teacher_id=?," +
                "graduateProjectTrueness_id=?,GraduateProjectStatus_id=?," +
                "graduateProjectSource_id=?,graduateProjectCategory_id=?,graduateProjectType_id=? " +
                "WHERE id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(update_sql);
        preparedStatement.setString(1,graduateProject.getTitle());
        preparedStatement.setDouble(2,graduateProject.getScore());
        preparedStatement.setInt(3,graduateProject.getYear());
        preparedStatement.setInt(4,graduateProject.getStudent().getId());
        preparedStatement.setInt(5,graduateProject.getTeacher().getId());
        preparedStatement.setInt(6,graduateProject.getGraduateProjectTrueness().getId());
        preparedStatement.setInt(7,graduateProject.getGraduateProjectStatus().getId());
        preparedStatement.setInt(8,graduateProject.getGraduateProjectSource().getId());
        preparedStatement.setInt(9,graduateProject.getGraduateProjectCategory().getId());
        preparedStatement.setInt(10,graduateProject.getGraduateProjectType().getId());
        preparedStatement.setInt(11,graduateProject.getId());
        int rowAffected = preparedStatement.executeUpdate();
        return rowAffected>0;
    }

    /**
     * 根据id查找毕设
     * @param connection
     * @param id
     * @return
     * @throws SQLException
     */
    public GraduateProject find(Connection connection, int id) throws SQLException {
        GraduateProject graduateProject = null;
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM graduateProject where id=?");
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            String title = resultSet.getString("title");
            double score = resultSet.getDouble("score");
            int year = resultSet.getInt("year");
            Student student = StudentService.getInstance().find(resultSet.getInt("student_id"));
            Teacher teacher = TeacherService.getInstance().find(resultSet.getInt("teacher_id"));
            GraduateProjectTrueness graduateProjectTrueness =
                    GraduateProjectTruenessDao.getInstance().find(connection,resultSet.getInt("graduateProjectTrueness_id"));
            GraduateProjectStatus graduateProjectStatus =
                    GraduateProjectStatusDao.getInstance().find(connection,resultSet.getInt("GraduateProjectStatus_id"));
            GraduateProjectSource graduateProjectSource =
                    GraduateProjectSourceDao.getInstance().find(connection,resultSet.getInt("graduateProjectSource_id"));
            GraduateProjectCategory graduateProjectCategory =
                    GraduateProjectCategoryDao.getInstance().find(connection,resultSet.getInt("graduateProjectCategory_id"));
            GraduateProjectType graduateProjectType =
                    GraduateProjectTypeDao.getInstance().find(connection,resultSet.getInt("graduateProjectType_id"));
            graduateProject =
                    new GraduateProject(id,title,score,year,
                            graduateProjectCategory,graduateProjectType,graduateProjectStatus,
                            graduateProjectSource,graduateProjectTrueness,teacher,student);
        }
        return graduateProject;
    }

    /**
     * 分页+条件查询毕设
     * @param connection
     * @param condition
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<GraduateProject> findAll(Connection connection , Collection<Condition> condition, Pagination pagination) throws SQLException {
        Collection<GraduateProject> graduateProjects = new TreeSet<>();
        //构建查找语句
        StringBuilder select_sql = new StringBuilder("SELECT * FROM GraduateProject ");
        if (condition != null){
            //构建条件查询where子句
            select_sql.append(Condition.toWhereClause(JSON.toJSONString(condition)));
            log.trace(select_sql.toString());
        }
        if (pagination != null){
            //获得元组总数
            int totalNum = CommonDao.getInstance().count(connection,"GraduateProject");
            //构建分页查询limit子句
            select_sql.append(pagination.toLimitClause(totalNum));
            log.trace(select_sql.toString());
        }
        PreparedStatement preparedStatement = connection.prepareStatement(select_sql.toString());
        //执行并获得查找到的结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //获得数据
        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String title = resultSet.getString("title");
            double score = resultSet.getDouble("score");
            int year = resultSet.getInt("year");
            Student student = StudentService.getInstance().find(resultSet.getInt("student_id"));
            Teacher teacher = TeacherService.getInstance().find(resultSet.getInt("teacher_id"));
            GraduateProjectTrueness graduateProjectTrueness =
                    GraduateProjectTruenessDao.getInstance().find(connection,resultSet.getInt("graduateProjectTrueness_id"));
            GraduateProjectStatus graduateProjectStatus =
                    GraduateProjectStatusDao.getInstance().find(connection,resultSet.getInt("GraduateProjectStatus_id"));
            GraduateProjectSource graduateProjectSource =
                    GraduateProjectSourceDao.getInstance().find(connection,resultSet.getInt("graduateProjectSource_id"));
            GraduateProjectCategory graduateProjectCategory =
                    GraduateProjectCategoryDao.getInstance().find(connection,resultSet.getInt("graduateProjectCategory_id"));
            GraduateProjectType graduateProjectType =
                    GraduateProjectTypeDao.getInstance().find(connection,resultSet.getInt("graduateProjectType_id"));
            graduateProjects.add(new GraduateProject(id,title,score,year,
                    graduateProjectCategory,graduateProjectType,graduateProjectStatus,
                    graduateProjectSource,graduateProjectTrueness,teacher,student));
        }
        return graduateProjects;
    }
}
